package com.gitee.zzh.sqllineage.ref;

import com.gitee.zzh.sqllineage.transform.ReplaceStarFields;
import com.gitee.zzh.sqllineage.util.ColumnInfoUtils;
import com.gitee.zzh.sqllineage.util.SqlStringUtils;
import com.gitee.zzh.sqllineage.vo.TransformVO;

import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

public class TableColumnRef extends SqlStringUtils {

    public static Set<String> getFirstTableColumn(TransformVO transformVO, Set<String> insertSet) {
        String text = transformVO.getText();
        String tableNameTemp = transformVO.getTableNameTemp();
        Set<String> tempSet = new HashSet<>();
        setUpdateField(insertSet, tempSet, transformVO);
        List<String> list = getInsertSqlText(text, tableNameTemp);
        String createAsSqlText = getCreateAsSqlText(text, tableNameTemp);
        if(!isBlank(createAsSqlText)) {
            list.add(createAsSqlText);
        }
        for (String sqlText : list) {
            if(hasStarExpression(sqlText)) {
                System.err.println("hasStarExpression(sqlText) " + formatSql(sqlText));
                continue;
            }
            String insertSqlColumns = ReplaceStarFields.getInsertColumns(transformVO, sqlText, createAsSqlText);
            if(insertSqlColumns == null) {
                continue;
            }
            sqlText = sqlText.substring(sqlText.indexOf(" SELECT "));
            List<String> selectList = getUnienSelectList(sqlText, true);
            for (String selectText : selectList) {
                Set<String> tempSet1 = getSelectInfo(insertSqlColumns, selectText, insertSet, transformVO);
                tempSet.addAll(tempSet1);
            }
        }
        return tempSet;
    }

    public static void getTableColumn(TransformVO transformVO, Set<String> tableColumnTempSet, Set<String> insertSet) {
        String text = transformVO.getText();
        Set<String> tempSet1 = new HashSet<>();
        while (!tableColumnTempSet.isEmpty()) {
            Set<String> tempSet2 = new HashSet<>();
            Set<String> tempSet3 = new HashSet<>();
            for (String tableColumnCombine : tableColumnTempSet) {
                String[] tableColumnComArr = tableColumnCombine.split(" ");
                String tableName = tableColumnComArr[0];
                String tableNameTemp = tableColumnComArr[1];
                transformVO.setTableName(tableName);
                transformVO.setTableNameTemp(tableNameTemp);
                setUpdateField(insertSet, tempSet2, transformVO);
                List<String> list = getInsertSqlText(text, tableNameTemp);
                String createAsSqlText = getCreateAsSqlText(text, tableNameTemp);
                if(!isBlank(createAsSqlText)) {
                    list.add(createAsSqlText);
                }
                for (String sqlText : list) {
                    if(hasStarExpression(sqlText)) {
                        System.err.println("hasStarExpression(sqlText) " + formatSql(sqlText));
                        continue;
                    }
                    String insertSqlColumns = ReplaceStarFields.getInsertColumns(transformVO, sqlText, createAsSqlText);
                    if(insertSqlColumns == null) {
                        continue;
                    }
                    sqlText = sqlText.substring(sqlText.indexOf(" SELECT "));
                    List<String> selectList = getUnienSelectList(sqlText, true);
                    for (String selectText : selectList) {
                        Set<String> tempSet4 = getSelectInfo1(tableColumnComArr, insertSqlColumns, selectText, insertSet, transformVO);
                        tempSet2.addAll(tempSet4);
                    }
                }
            }
            for (String s : tempSet2) {
                if(!tempSet1.contains(s)) {
                    tempSet3.add(s);
                }
            }
            tempSet1.addAll(tempSet3);
            tableColumnTempSet = tempSet3;
        }
    }

    private static Set<String> getSelectInfo(String insertSqlColumns, String selectText, Set<String> insertSet, TransformVO transformVO) {
        Set<String> tableSet = getTableBySelect(selectText);
        String text = transformVO.getText();
        List<String> sourceTableList = tableSet.stream().filter(s->!isTempTable(text, s)).collect(Collectors.toList());
        List<String> tempTableList = tableSet.stream().filter(s->isTempTable(text, s)).collect(Collectors.toList());
        Set<String> tempSet = new HashSet<>();
        String selectColumns = selectText.substring(selectText.indexOf("SELECT ") + "SELECT ".length(), selectText.indexOf(" FROM "));
        String[] insertSqlColumnsArr = insertSqlColumns.split(",");
        String[] selectColumnsArr = selectColumns.split(",");
        if(insertSqlColumnsArr.length != selectColumnsArr.length) {
            System.err.println(insertSqlColumns + "\n" + selectColumns + "\n" + insertSqlColumnsArr.length + " : " + selectColumnsArr.length);
            return tempSet;
        }
        int len = insertSqlColumnsArr.length;
        for (int i = 0; i < len; i++) {
            String targetColumn = insertSqlColumnsArr[i];
            String sourceColumn = selectColumnsArr[i];
            if(sourceColumn.contains("_MANUAL_S_")) {
                setSelectSelectFields(sourceColumn, targetColumn, insertSet, tempSet, transformVO);
            }
            add(sourceTableList, targetColumn, sourceColumn, selectText, insertSet, transformVO);
            add(tempTableList, targetColumn, sourceColumn, selectText, tempSet, transformVO);
        }
        return tempSet;
    }
    private static Set<String> getSelectInfo1(String[] tableNameTempComArr, String insertSqlColumns, String selectText, Set<String> insertSet, TransformVO transformVO) {
        Set<String> tableSet = getTableBySelect(selectText);
        String text = transformVO.getText();
        List<String> sourceTableList = tableSet.stream().filter(s->!isTempTable(text, s)).collect(Collectors.toList());
        List<String> tempTableList = tableSet.stream().filter(s->isTempTable(text, s)).collect(Collectors.toList());
        Set<String> tempSet = new HashSet<>();
        String selectColumns = selectText.substring(selectText.indexOf("SELECT ") + "SELECT ".length(), selectText.indexOf(" FROM "));
        String[] insertSqlColumnsArr = insertSqlColumns.split(",");
        String[] selectColumnsArr = selectColumns.split(",");
        if(insertSqlColumnsArr.length != selectColumnsArr.length) {
            System.err.println(insertSqlColumns + "\n" + selectColumns + "\n" + insertSqlColumnsArr.length + " : " + selectColumnsArr.length);
            return tempSet;
        }
        transformVO.setTableName(tableNameTempComArr[0]);
        int len = insertSqlColumnsArr.length;
        String tableColumn = tableNameTempComArr[2];
        for (int i = 0; i < len; i++) {
            String targetColumn = insertSqlColumnsArr[i];
            String sourceColumn = selectColumnsArr[i];
            if(!tableNameTempComArr[3].equals(targetColumn)) {
                continue;
            }
            if(sourceColumn.contains("_MANUAL_S_")) {
                setSelectSelectFields(sourceColumn, tableColumn, insertSet, tempSet, transformVO);
            }
            add(sourceTableList, tableColumn, sourceColumn, selectText, insertSet, transformVO);
            add(tempTableList, tableColumn, sourceColumn, selectText, tempSet, transformVO);
        }
        return tempSet;
    }
    private static void setSelectSelectFields(String sourceColumn, String tableColumn, Set<String> insertSet, Set<String> tempSet, TransformVO transformVO) {
        if(!sourceColumn.contains("_MANUAL_S_")) {
            return;
        }
        String text = transformVO.getText();
        String[] textArr = transformVO.getTextArr();
        String[] arr = sourceColumn.split("[() |=]");
        for (String s : arr) {
            if(!s.contains("_MANUAL_S_")) {
                continue;
            }
            for (String textPart : textArr) {
                textPart = textPart.trim();
                if(!textPart.contains("CREATE TABLE " + s + " AS SELECT ")) {
                   continue;
                }
                String selectSql = textPart.substring(("CREATE TABLE " + s + " AS SELECT ").length());
                List<String> selectList = getUnienSelectList(selectSql, true);
                for (String sqlText : selectList) {
                    Set<String> tableSet = getTableBySelect(sqlText);
                    List<String> sourceTableList = tableSet.stream().filter(st->!isTempTable(text, st)).collect(Collectors.toList());
                    List<String> tempTableList = tableSet.stream().filter(st->isTempTable(text, st)).collect(Collectors.toList());
                    String sourceColumn1 = sqlText.substring(0, sqlText.indexOf(" FROM "));
                    add(sourceTableList, tableColumn, sourceColumn1, sqlText, insertSet, transformVO);
                    add(tempTableList, tableColumn, sourceColumn1, sqlText, tempSet, transformVO);
                }
            }
        }
    }

    public static void setUpdateField(Set<String> insertSet, Set<String> tempSet, TransformVO transformVO) {
        String text = transformVO.getText();
        String tableName = transformVO.getTableName();
        String tableNameTemp = transformVO.getTableNameTemp();
        List<String> sqlList = getUpdateSqlText(text, tableNameTemp);
        List<Map<String, String>> targetList = ColumnInfoUtils.getColumnsInfo(transformVO, tableNameTemp);
        if(targetList.isEmpty()) {
            return;
        }
        for (String textUpdate : sqlList) {
            String left = substringBySymbol(textUpdate.trim(), " WHERE ");
            String sourceTable = getUpdateTable(left);
            List<Map<String, String>> sourceList = ColumnInfoUtils.getColumnsInfo(transformVO, sourceTable);
            if(sourceList.isEmpty()) {
                continue;
            }
            String textMiddle = left.substring(left.indexOf(" SET ") + " SET ".length());
            textMiddle = replaceFunc(textMiddle);
            String[] parts = textMiddle.split(",");
            for (String part : parts) {
                if(!part.contains("=")) {
                    break;
                }
                String leftPart = part.substring(0, part.indexOf("="));
                leftPart = substringByLastSymbol(leftPart, " ", ".");
                String rightPart = part.substring(part.indexOf("=") + "=".length());
                for (Map<String, String> map : targetList) {
                    String tColumn = map.get("COLUMN_NAME");
                    if(!leftPart.equals(tColumn)) {
                        continue;
                    }
                    for (Map<String, String> map1 : sourceList) {
                        String sColumn = map1.get("COLUMN_NAME");
                        if(!contains(rightPart, sColumn)) {
                            continue;
                        }
                        String sCombine = tableName + " " + sourceTable + " " + tColumn + " " + sColumn;
                        if(!text.contains("DROP TABLE IF EXISTS " + sourceTable + ";")) {
                            insertSet.add(sCombine);
                        } else {
                            tempSet.add(sCombine);
                        }
                    }
                }
            }
        }
    }

    private static void add(List<String> tableList, String tableColumn, String sourceColumn, String selectText,
                            Set<String> insertOrTempSet, TransformVO transformVO) {
        String tableName = transformVO.getTableName();
        for (String sourceTable : tableList) {
            List<Map<String, String>> columnList = ColumnInfoUtils.getColumnsInfo(transformVO, sourceTable);
            for (Map<String, String> map : columnList) {
                String column = map.get("COLUMN_NAME");
                String sCombine0 = tableName + " " + sourceTable + " " + tableColumn + " " + column;
                if(insertOrTempSet.contains(sCombine0)){
                    continue;
                }
                if(sourceColumn.endsWith(" " + column)) {
                    sourceColumn = sourceColumn.substring(0, sourceColumn.length() - column.length() - 1);
                }
                if(containsLeft(sourceColumn, "." + column, "|", "=")) {
                    List<String> aliasList = getWordAlias(sourceColumn, column);
                    for (String alias : aliasList) {
                        if(containsLeft(selectText, sourceTable + " " + alias, " ", ")")
                                || containsLeft(selectText, sourceTable + " AS " + alias, " ", ")")) {
                            String sCombine = tableName + " " + sourceTable + " " + tableColumn + " " + column;
                            insertOrTempSet.add(sCombine);
                            break;
                        }
                    }
                } else {
                    if(contains(sourceColumn, column)) {
                        String sCombine = tableName + " " + sourceTable + " " + tableColumn + " " + column;
                        insertOrTempSet.add(sCombine);
                    }
                }
            }
        }
    }
}
